#导入pymysql模块
import pymysql
#获取数据库连接
db=pymysql.connect(host="localhost",user="root",password="southward",db="mydb",charset="utf8")
#创建游标对象
cursor=db.cursor()

while True:
	
	cursor.execute("select * from stu")

	data=cursor.fetchall()
    #定义学生信息集合alist
	alist=[]
	for info in data:
		alist.append(info)

    #定义一个学生信息的输出函数
	def showStu(alist):
		'''
		学生信息的输出函数
		'''
		if len(alist)==0:
			print("========== 没有学员信息可以输出！=============")
			return
		print("|{0:<5}| {1:<10}| {2:<5}| {3:<10}|".format("sid","name","age","classid"))
		print("-"*40)
		for a in range(len(alist)):
			print("|{0:<5}| {1:<10}| {2:<5}| {3:<10}|".format(alist[a][0],alist[a][1],alist[a][2],alist[a][3]))



	# 输出初始界面
	print("="*12,"学员管理系统","="*14)
	print("{0:1} {1:13} {2:15}".format(" ","1. 查看学员信息","2. 添加学员信息"))
	print("{0:1} {1:13} {2:15}".format(" ","3. 删除学员信息","4. 退出系统"))
	print("="*40)
	key = input("请输入对应的选择：")
	# 根据键盘值，判断并执行对应的操作
	if key == "1":
		print("="*12,"学员信息浏览","="*14)
		showStu(alist)
		input("按回车键继续：")
	elif key == "2":
		print("="*12,"学员信息添加","="*14)
		b1=input("请输入要添加的姓名：")
		b2=input("请输入要添加的年龄：")
		b3=input("请输入要添加的班级号：")
		dataa=(0,b1,int(b2),b3)
		sqla="insert into stu(id,name,age,classid) values('%d','%s','%d','%s')"%(dataa)
		try:
			m=cursor.execute(sqla)
			db.commit()
			print("已成功添加条数：",m)
		except Exception as err:
			db.rollback()
			print("SQL执行错误，原因：",err)
		input("按回车键继续：")
	elif key == "3":
		print("="*12,"学员信息删除","="*14)
		showStu(alist)
		sid = input("请输入你要删除的信息id号：")
		sqlb="delete from stu where id=%d"%(int(sid))
		try:
			cursor.execute(sqlb)
			db.commit()
			print("成功删除条数",cursor.rowcount)
		except Exception as err:
			db.rollback()
			print("SQL执行错误，原因：",err)
		input("按回车键继续：")
	elif key == "4":
		print("="*12,"再见","="*14)
		break
	else:
		print("======== 无效的键盘输入！ ==========")
	



db.close





    
